﻿using Coldairarrow.Entity.Base;
using Coldairarrow.Entity.Base_Manage;
using Coldairarrow.Entity.DTO;
using Coldairarrow.Entity.PB;
using Coldairarrow.Entity.Print;
using Coldairarrow.IBusiness;
using Coldairarrow.Util;
using Coldairarrow.Util.ClassLibrary;
using Coldairarrow.Util.Helper;
using EFCore.Sharding;
using LinqKit;
using Microsoft.EntityFrameworkCore;
using System;
using System.Collections.Generic;
using System.Data;
using System.Linq;
using System.Linq.Dynamic.Core;
using System.Linq.Expressions;
using System.Text;
using System.Threading.Tasks;

namespace Coldairarrow.Business.PB
{
    public partial class PB_BuildingnumberBusiness : BaseBusiness<PB_Buildingnumber>, IPB_BuildingnumberBusiness, ITransientDependency
    {
        readonly IOperator _operator;
        public PB_BuildingnumberBusiness(IDbAccessor db, IOperator @operator)
            : base(db)
        {
            _operator = @operator;
        }

        #region 外部接口
        public async Task AddDataExlAsync(List<PB_Buildingnumber> list)
        {
            await Db.InsertAsync<PB_Buildingnumber>(list);
            //await Db.InsertAsync(list);

            //try
            //{
            //    BulkInsert(list);
            //    //await Db.InsertAsync<PB_Project>(list);
            //    //return Db.InsertAsync(list);
            //}
            //catch (Exception ex)
            //{

            //    throw;
            //}
        }
        public async Task<PageResult<PB_Buildingnumber>> GetDataListAsync(PageInput<ConditionDTO> input)
        {
            var q = GetIQueryable();
            var where = LinqHelper.True<PB_Buildingnumber>();
            var search = input.Search;

            //筛选
            if (!search.Condition.IsNullOrEmpty() && !search.Keyword.IsNullOrEmpty())
            {
                var newWhere = DynamicExpressionParser.ParseLambda<PB_Buildingnumber, bool>(
                    ParsingConfig.Default, false, $@"{search.Condition}.Contains(@0)", search.Keyword);
                where = where.And(newWhere);
            }

            return await q.Where(where).GetPageResultAsync(input);
        }


        public async Task<PB_Buildingnumber> GetTheDataAsync(string id)
        {

            return await GetEntityAsync(id);
        }

        [DataAddLog(UserLogType.楼号管理, "Buildingnumber", "项目管理")]
        public async Task AddDataAsync(PB_Buildingnumber data)
        {

            data.IsInspection = false;
            var atAllData = Db.GetListBySqlAsync<PB_Buildingnumber>("select * from PB_Buildingnumber where ProjectId='" + data.ProjectId + "' and Buildingnumber='" + data.Buildingnumber + "'").Result;
            if (atAllData.Count > 0)
            {
                throw new BusException("楼号重复");
            }

            await InsertAsync(data);
        }
        [DataEditLog(UserLogType.楼号管理, "Buildingnumber", "项目管理")]

        public async Task UpdateDataAsync(PB_Buildingnumber data)
        {
            await UpdateAsync(data);
        }

        [DataDeleteLog(UserLogType.楼号管理, "Buildingnumber", "楼号管理")]
        public async Task DeleteDataAsync(List<string> ids)
        {
            var pb_designlist =await Db.GetIQueryable<PB_DesignList>().Where(m=>ids.Contains(m.BuildingNumberID)).ToListAsync();
            if (pb_designlist.Count>0) {
                throw new BusException("楼栋信息已有关联设计清单禁止删除!");
            }
            await DeleteAsync(ids);
        }

        public async Task<List<PB_BuildingnumberDTO>> GetDataList(string ProjectId)
        {
            Expression<Func<PB_Buildingnumber, PB_Project, PB_BuildingnumberDTO>> select = (a, b) => new PB_BuildingnumberDTO
            {

                ProjectIName = b.Name
            };
            select = select.BuildExtendSelectExpre();
            var q = from a in GetIQueryable().AsExpandable()
                    join f in Db.GetIQueryable<PB_Project>() on a.ProjectId equals f.Id into af
                    from f in af.DefaultIfEmpty()
                    select @select.Invoke(a, f);
            //筛选
            if (!ProjectId.IsNullOrEmpty())
            {
                q = q.Where(w => w.ProjectId == ProjectId);
            }
            var res = await q.ToListAsync();

            return res;
        }

        public IQueryable<T> GetQueryableOK<T>() where T : class, new()
        {
            return Db.GetIQueryable<T>();
        }

        public async Task<AjaxResult> PushMessage(PB_BuildingnumberTiper data, MongoDBOptions _mongodbOptions)
        {
            try
            {
                List<string> List = new List<string>();
                foreach (var item in data.Build)
                {
                    List.Add(item);
                }
                var q = GetIQueryable();
                List<MessageNotice> printqueue = new List<MessageNotice>();
                var res = q = q.Where(m => List.Contains(m.Id));
                var ress = Db.GetIQueryable<PB_Buildingnumber>().Where(m
                     => List.Contains(m.Id)).FirstOrDefault();
                var projectName = Db.GetIQueryable<PB_Project>().Where(m => m.Id == ress.ProjectId).FirstOrDefault();
                foreach (var item in res)
                {
                    PB_Buildingnumber pB_Buildingnumber = new PB_Buildingnumber();
                    MessageNotice printdata = new MessageNotice();
                    printdata.Id = IdHelper.GetId();
                    printdata.DeviceId = data.DeviceId;
                    printdata.PlanId = "";
                    printdata.ProjectId = projectName.Name;
                    printdata.BuildingnumberId = ress.Buildingnumber;
                    printdata.MessType = "";
                    printdata.Message = data.Message;
                    printdata.MessDate = DateTime.Now;
                    printdata.PushDept = data.ReceiveDept;
                    printdata.ReceiveDept = data.ReceiveDept;
                    printqueue.Add(printdata);
                }
                MongoDBHelper mongo = new MongoDBHelper(_mongodbOptions);
                string Time = DateTime.Today.Year.ToString() + DateTime.Today.Month.ToString();
                await mongo.InsertManyAsync<MessageNotice>(MessageNoticeTips + Time, printqueue);
                AjaxResult resIn = new AjaxResult
                {
                    Success = true,
                    Msg = "推送成功"
                };
                return resIn;
            }
            catch (Exception)
            {
                AjaxResult res = new AjaxResult
                {
                    Success = false,
                    Msg = "推送失败"
                };
                return res;
            }
        }

        public async Task<PB_Buildingnumber> PB_BuildingnumberDetails(string ProjectId, string BuildingnumberId)
        {
            var Data = GetIQueryable().Where(m => m.ProjectId == ProjectId && m.Id == BuildingnumberId).FirstOrDefault();
            return Data;

        }
        //钉钉项目现场进度获取文本描述
        public async Task<List<Base_EnumItem>> GetTextList()
        {
            var list = await Db.GetIQueryable<Base_EnumItem>().Where(m => m.EnumCode == "TextDescription").ToListAsync();
            return list;
        }

        public async Task PatrolStatus(string BuildId, bool IsInspection)
        {
            string sqlstr = "";
            if (IsInspection == true)
            {
                sqlstr = "update PB_Buildingnumber set IsInspection=1 where Id='" + BuildId + "';";
                await ExecuteSqlAsync(sqlstr);

                List<DD_MessageDTO> users = new List<DD_MessageDTO>();
                List<BaseDto> userss = new List<BaseDto>();
                List<BaseDto> userss3 = new List<BaseDto>();
               
                    StringBuilder sql = new StringBuilder();
                    sql.Append("SELECT ");
                    sql.Append("a.ProjectId,");
                    sql.Append("c.NAME ProjectName,");
                    sql.Append("a.Id BuildingId,");
                    sql.Append("a.Buildingnumber,");
                    sql.Append("d.DingUserId TechnicalDirector,");
                    sql.Append("a.Collaborators,");
                    sql.Append("b.CreatorId, ");
                    sql.Append("e.DingUserId ProjectDirector ");
                    sql.Append("FROM ");
                    sql.Append("PB_Buildingnumber a ");
                    sql.Append("LEFT JOIN (");
                    sql.Append("SELECT ");
                    sql.Append("CreatorId,");
                    sql.Append("ProjectId,");
                    sql.Append("BuildingnumberId ");
                    sql.Append("FROM ");
                    sql.Append("PB_ProjectStatuLog ");
                    sql.Append("WHERE ");
                    sql.Append("to_days( CreateTime ) = to_days(");
                    sql.Append("now())) b ON a.ProjectId = b.ProjectId ");
                    sql.Append("AND a.Id = b.BuildingnumberId ");
                    sql.Append("LEFT JOIN PB_Project c ON a.ProjectId = c.Id ");
                    sql.Append("LEFT JOIN Base_User d ON a.TechnicalDirector = d.Id ");
                    sql.Append("LEFT JOIN Base_User e ON a.ProjectDirector = e.Id ");
                    sql.Append("WHERE ");
                    sql.Append("a.TechnicalDirector IS NOT NULL ");
                    sql.Append("AND b.CreatorId IS NULL ");
                 sql.Append("AND a.Id ='"+ BuildId + "' ");
                users = await Db.GetListBySqlAsync<DD_MessageDTO>(sql.ToString());
                    List<string> usrs = new List<string>();
                    foreach (var item in users)
                    {
                        if (item.TechnicalDirector != null)
                        {
                            usrs.Add(item.TechnicalDirector);

                        }
                        if (item.ProjectDirector != null)
                        {
                            usrs.Add(item.ProjectDirector);
                        }
                    //项目主管 技术负责人调用
                    Util.Helper.DingInputDTO dto = new Util.Helper.DingInputDTO();
                        dto.ExecutorIds = usrs;
                        dto.Str = "[" + DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss") + "]" + item.ProjectName + "(" + item.Buildingnumber + ")待更新进度";
                        dto.MessgUrl = "pages/progress/progress?pname=" + item.ProjectName + "&bname=" + item.Buildingnumber + "&id=" + item.ProjectId + "," + item.BuildingId + ""; ;
                        DingHelper.sendMessage(dto);
                    //分割获取协作者  跳转路径  dto.MessgUrl = "eapp:pages/index/index";
                    if (item.Collaborators != null)
                    {
                        //创建协作者跳转页
                        Util.Helper.DingInputDTO CollaboratorsDto = new Util.Helper.DingInputDTO();
                        string[] CollaboratorslIST = item.Collaborators.Split(',');
                        string _where = "";
                        foreach (var coll in CollaboratorslIST)
                        {
                            _where += "'" + coll + "',";
                        }
                        StringBuilder sql4 = new StringBuilder();
                        sql4.Append("select DingUserId  ");
                        sql4.Append("from ");
                        sql4.Append("Base_User  where id in (" + _where.Substring(0, _where.Length - 1) + ")");
                        var userss4 = await Db.GetListBySqlAsync<BaseDto>(sql4.ToString()) ;

                        List<string> Xiezuos = new List<string>();
                        foreach (var xiezuo in userss4)
                        {
                            Xiezuos.Add(xiezuo.DingUserId);
                        }
                        mesgurl mesgurl = new mesgurl();
                        mesgurl.ProjectName = item.ProjectName;
                        mesgurl.Buildingnumber = item.Buildingnumber;
                        mesgurl.Bulid = item.BuildingId;
                        string temp = "";
                        temp = "{ProjectName:"+item.ProjectName+ ",Buildingnumber:"+item.Buildingnumber+ ",Bulid:"+item.BuildingId+"}";

                               
                        CollaboratorsDto.ExecutorIds = Xiezuos;
                        CollaboratorsDto.Str = "[" + DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss") + "]" + item.ProjectName + "(" + item.Buildingnumber + ")待更新进度";
                        CollaboratorsDto.MessgUrl = "pages/progressRule/progressRule?obj="+ temp + " ";
                        string aa= "pages/progressRule/progressRule?obj="+ temp + "  ";
                        DingHelper.sendMessage(CollaboratorsDto);

                    }
                    }

                }
            else if (IsInspection == false)
            {
                sqlstr = "update PB_Buildingnumber set IsInspection=0 where Id='" + BuildId + "';";
                await ExecuteSqlAsync(sqlstr);

            }

        }

        /// <summary>
        /// 钉钉列表页获取当前登录人的项目menu
        /// </summary>
        /// <param name="DingUserId"></param>
        /// <returns></returns>
        /// 


        public async Task<PageResult<BuildingnumberMenuDto>> GetBulidMenuList(PageInput<IT_LocalDetailDtolistContionDTO> input, string DingUserId)
        {
            string sqlMain = @"
   SELECT  T.* FROM (SELECT
	P.`Name` ProjectName,
	P.Id ProjectId,
	B.Id Bulid,
	B.IsInspection IsInspection,
	B.Buildingnumber AS Buildingnumber,
	U1.DingUserId U1DingUserId,
	U2.DingUserId AS U2DingUserId,
	U3.DingUserId AS U3DingUserId,CASE	WHEN U1.DingUserId = '" + DingUserId + "' THEN  0 WHEN U2.DingUserId = '"+DingUserId+"' THEN 0 ELSE 1 END Url FROM PB_Buildingnumber AS B  LEFT JOIN PB_Project AS P ON B.ProjectId = P.Id LEFT JOIN Base_User AS U1 ON B.TechnicalDirector = U1.Id  LEFT JOIN Base_User AS U2 ON B.ProjectDirector = U2.Id  LEFT JOIN Base_User AS U3 ON B.Collaborators = U3.Id WHERE  U1.DingUserId = '"+DingUserId+"' OR U2.DingUserId = '"+DingUserId+"' OR U3.DingUserId LIKE '%"+DingUserId+"%')  AS T WHERE T.IsInspection = 1";
            var res = await Db.GetListBySqlAsync<BuildingnumberMenuDto>(sqlMain);

            int pagecount;//总页数
            List<BuildingnumberMenuDto> listpro_down = res.ToList();//一个要分页的，类型数据集合，存储的是所有的数据
            List<BuildingnumberMenuDto> productes = new List<BuildingnumberMenuDto>();//用来存储要展示的数据
            int pagees = listpro_down.Count;//所有数据的总条数
                                            //计算总页数
            if (pagees % input.PageRows == 0)
            {
                pagecount = pagees / input.PageRows;
            }
            else
            {
                pagecount = (pagees / input.PageRows) + 1;
            }
            productes = listpro_down.Skip((input.PageIndex - 1) * input.PageRows).Take(input.PageRows).ToList();//分页的重点
            return new PageResult<BuildingnumberMenuDto> { Data = productes, Total = pagees };
        }
        public async Task<PageResult<BuildingnumberMenuDto>> GetBulidMenuListS(PageInput<IT_LocalDetailDtolistContionDTO> input, string DingUserId) {
            Expression<Func<PB_Buildingnumber, PB_Project, Base_User, Base_User, Base_User, BuildingnumberMenuDto>> select = (a, b, c, d, e) => new BuildingnumberMenuDto
            {
                IsInspection= (bool)a.IsInspection,
                ProjectName = b.Name,
                ProjectId = b.Id,
                Buildingnumber = a.Buildingnumber,
                Bulid = a.Id,
                U1DingUserId = c.DingUserId,
                U2DingUserId = d.DingUserId,
                U3DingUserId = e.DingUserId,
            };
            select = select.BuildExtendSelectExpre();
            var q = from a in Db.GetIQueryable<PB_Buildingnumber>().AsExpandable()
                    join b in Db.GetIQueryable<PB_Project>() on a.ProjectId equals b.Id into ab
                    from b in ab.DefaultIfEmpty()
                    join c in Db.GetIQueryable<Base_User>() on a.TechnicalDirector equals c.Id into ac
                    from c in ac.DefaultIfEmpty()
                    join d in Db.GetIQueryable<Base_User>() on a.ProjectDirector equals d.Id into ad
                    from d in ad.DefaultIfEmpty()
                    join e in Db.GetIQueryable<Base_User>() on a.Collaborators equals e.Id into ae
                    from e in ae.DefaultIfEmpty()
                    select @select.Invoke(a, b, c, d, e);
            q = q.Where(m => m.U1DingUserId == DingUserId || m.U2DingUserId == DingUserId || m.U3DingUserId.Contains(DingUserId));
            q = q.Where(m=>m.IsInspection == true);
            foreach (var item in q) {
                if (item.U1DingUserId==DingUserId||item.U2DingUserId==DingUserId) {
                    item.Url = 0;
                }
                else {
                    item.Url = 1;
                }
            }
            return await q.GetPageResultAsync(input);

        }

        /// <summary>
        /// 时间范围选择条件可传可不传 出差明细列表统计数据
        /// </summary>
        /// <param name="StartTime"></param>
        /// <param name="EndTime"></param>
        /// <returns></returns>
        public async Task<List<PersonnelListDto>> GetPersonnelList(string StartTime, string EndTime)
        {
            StringBuilder sql = new StringBuilder();
            sql.Append("SELECT min( `a`.`CreateTime` ) AS `StartDate`, ");
            sql.Append("max( `a`.`CreateTime` ) AS `EndDate`, ");
            sql.Append("`a`.`ProjectId` AS `ProjectId`,  ");
            sql.Append("`a`.`BuildingnumberId` AS `BuildingnumberId`,  ");
            sql.Append("`a`.`CreatorId` AS `CreatorId`, ");
            sql.Append("`b`.`RealName` AS `RealName`, ");
            sql.Append("`b`.`UserPost` AS `UserPost`, ");
            sql.Append("`c`.`Name` AS `DepartmentName`, ");
            sql.Append("`p`.`Name` AS `ProjectName`, ");
            sql.Append("`R`.`Name` AS  RName, ");
            sql.Append("`Rs`.`Name` AS RsName, ");
            sql.Append("`p`.`Address` AS `Address`, ");
            sql.Append("( CASE ");
            sql.Append("WHEN ( timestampdiff( DAY, min( `a`.`CreateTime` ), max( `a`.`CreateTime` )) = 0 ) THEN  1 ");
            sql.Append("ELSE timestampdiff( DAY, min( `a`.`CreateTime` ), max( `a`.`CreateTime` ))  ");
            sql.Append("END  ) AS `days` ");
            sql.Append("FROM ");
            sql.Append("((( ");
            sql.Append("`PB_ProjectStatuLog` `a` JOIN `Base_User` `b` ON ((`a`.`CreatorId` = `b`.`Id` ))) ");
            sql.Append("JOIN `Base_Department` `c` ON ((`b`.`DepartmentId` = `c`.`Id` )))  ");
            sql.Append("JOIN `PB_Project` `p` ON ((`a`.`ProjectId` = `p`.`Id` )) ");
            sql.Append("LEFT JOIN `Base_Region` `R` ON ((`p`.`ProvinceId` = `R`.`Id` ))  ");
            sql.Append("LEFT JOIN `Base_Region` `Rs` ON ((`p`.`CityId` = `Rs`.`Id` )) ");
            sql.Append(") ");
            sql.Append("WHERE ");
            sql.Append("1 = 1 ");
            if (!StartTime.IsNullOrEmpty() && !EndTime.IsNullOrEmpty()) {
                sql.Append("AND  `a`.`CreateTime` BETWEEN '"+ StartTime + "' AND '"+ EndTime + "' ");
            }
            sql.Append("GROUP BY ");
            sql.Append("`a`.`ProjectId`, ");
            sql.Append("`a`.`BuildingnumberId`, ");
            sql.Append("`a`.`CreatorId` ");
            List<PersonnelListDto> List = await Db.GetListBySqlAsync<PersonnelListDto>(sql.ToString());
            if (List.Count > 0)
            {
                return List;
            }
            else {
                return null;
            }
           
        }

        /// <summary>
        /// 获取最新一条项目进度列表
        /// </summary>
        /// <param name="ProjectId">项目id</param>
        /// <param name="Bulid">楼号id</param>
        /// <returns></returns>
        public async Task<PB_ProjectStatuLoDto> GetArticle1(string ProjectId, string Bulid)
        {
            //获取最新的一条信息
            //var firTo1 =await Db.GetIQueryable<PB_ProjectStatuLoDto>().Where(m => m.ProjectId == ProjectId && m.BuildingnumberId == Bulid).OrderByDescending(m => m.CreateTime).FirstOrDefaultAsync();

            Expression<Func<PB_ProjectStatuLog, PB_Project,PB_Buildingnumber, PB_ProjectStatuLoDto>> select = (a, b,c) => new PB_ProjectStatuLoDto
            {

                ProjectName=b.Name,
                BulidNo=c.Buildingnumber,
                Qishi=1,
                Zhongzhi=1,
               StandsNumber =c.StandsNumber,
                LiftPlatNums =c.LiftPlatNums,
               };
            select = select.BuildExtendSelectExpre();
            var q = from a in Db.GetIQueryable<PB_ProjectStatuLog>().AsExpandable()
                    join b in Db.GetIQueryable<PB_Project>() on a.ProjectId equals b.Id into ab
                    from b in ab.DefaultIfEmpty()
                    join c in Db.GetIQueryable<PB_Buildingnumber>() on a.BuildingnumberId equals c.Id into ac
                    from c in ac.DefaultIfEmpty()
                    select @select.Invoke(a,b,c);
            PB_ProjectStatuLoDto firTo1 = new PB_ProjectStatuLoDto();
            firTo1 =await q.Where(m=>m.ProjectId==ProjectId&&m.BuildingnumberId==Bulid).OrderByDescending(m=>m.CreateTime).FirstOrDefaultAsync();
            if (firTo1==null) {
                throw new Exception("暂无数据");
            }
            return firTo1;

        }

        /// <summary>
        /// 传入项目id楼号id验证今日是否已进度记录
        /// </summary>
        /// <param name="ProjectId">项目id</param>
        /// <param name="Bulid">楼号id</param>
        /// <returns></returns>
        public async Task<bool> Result(string ProjectId, string Bulid)
        {
            var aa =await Db.GetIQueryable<PB_ProjectStatuLog>().Where(m=>m.ProjectId==ProjectId&&m.BuildingnumberId==Bulid).OrderByDescending(m=>m.CreateTime).FirstOrDefaultAsync();
            if (aa == null)
            {
                return false;
            }
            //获取项目楼号对应的最新一条数据
            string Times = aa.CreateTime.ToString("yyyy-MM-dd");
            string Time = DateTime.Now.ToString("yyyy-MM-dd");
            if (Times == Time)
            {
                return true;
            }
            else
                return false;



        }

        public async Task<bool> ResultTime(string Bulid)
        {
            var aa = await Db.GetIQueryable<PB_ProjectLogging>().Where(m =>  m.BuildingNumberID == Bulid).OrderByDescending(m => m.CreateTime).FirstOrDefaultAsync();
            if (aa == null)
            {
                return false;
            }
            //获取项目楼号对应的最新一条数据
            string Times = aa.CreateTime.ToString("yyyy-MM-dd");
            string Time = DateTime.Now.ToString("yyyy-MM-dd");
            if (Times == Time)
            {
                return true;
            }
            else
                return false;

        }

        /// <summary>
        /// 移交推送消息实体
        /// </summary>
        /// <param name="data">BuildingNumberID 楼号id  TransferorId 移交人  HandoverPusher 推送人</param>
        /// <returns></returns>
        public async  Task Transfer(BuildingnumberTransfer data)
        {
           //获取移交人数据
            var TransferorId =await Db.GetIQueryable<Base_User>().Where(m=>m.DingUserId==data.DingUserID).FirstOrDefaultAsync();
            if (TransferorId==null) {
                return;
            }
            //更改移交人
            var PB_Buildingnumber = await Db.GetIQueryable<PB_Buildingnumber>().Where(m=>m.TechnicalDirector== TransferorId.Id&&m.Id==data.BuildingNumberID).FirstOrDefaultAsync() ;
            if (PB_Buildingnumber != null)
            {
                //验证楼号表技术负责人 和楼号id
                string SqlupdateTransfer = "UPDATE PB_Buildingnumber set TechnicalDirector='" +data.HandoverPusher+"' WHERE  TechnicalDirector='" + TransferorId.Id + "'  AND Id='" + data.BuildingNumberID + "'";
                await Db.ExecuteSqlAsync(SqlupdateTransfer);
            }
            var PB_Buildingnumbers = await Db.GetIQueryable<PB_Buildingnumber>().Where(m => m.ProjectDirector == TransferorId.Id && m.Id == data.BuildingNumberID).FirstOrDefaultAsync();
            if (PB_Buildingnumbers != null) {
                //验证楼号表项目主管
                string SqlupdateTransfers = "UPDATE PB_Buildingnumber set ProjectDirector='" + data.HandoverPusher + "' WHERE  ProjectDirector='" + TransferorId.Id + "'  AND Id='" + data.BuildingNumberID + "'";
                await Db.ExecuteSqlAsync(SqlupdateTransfers);
            }
    }


        /// <summary>
        /// 获取部门下用户树状列表
        /// </summary>
        /// <returns></returns>

        public async Task<List<BaseTreeDto>> GetBaseTreeListAsyncs()
        {
            var list = await Db.GetIQueryable<Base_Department>().Where(m=>m.Type==6).ToListAsync();
            List<BaseTreeDto> listData = new List<BaseTreeDto>();
            foreach (var item in list)
            {
                BaseTreeDto tree = new BaseTreeDto();
                tree.Id = item.Id;
                tree.Text = item.Name;
                tree.Value = item.Id;
                listData.Add(tree);
                await GetItemTree(item.Id, listData);
            }
            var treeList = listData
                .Select(x => new BaseTreeDto
                {
                    Id = x.Id,
                    ParentId = x.ParentId,
                    Text = x.Text,
                    Value = x.Id,
                }).ToList();
            return TreeHelper.BuildTree(treeList);

        }
            /// <summary>
            /// 获取部门下人员树状列表
            /// </summary>
            /// <param name="ParentId">部门id</param>
            /// <param name="ListData">数据集合</param>
            /// <returns></returns>
            public async Task<List<BaseTreeDto>> GetItemTree(string DepartmentId, List<BaseTreeDto> ListData)
        {
            var childrenItem = await Db.GetIQueryable<Base_User>().Where(x => x.DepartmentId == DepartmentId).ToListAsync();
            foreach (var item in childrenItem)
            {
                BaseTreeDto treedata = new BaseTreeDto();
                treedata.Id = item.Id;
                treedata.ParentId = item.DepartmentId;
                treedata.Text = "("+item.UserCode+")" + item.RealName;
                treedata.Value = item.Id;
                ListData.Add(treedata);
            }
            return ListData;
        }

        public async Task UpdateBuliNo(UpdateBuliNo data)
        {
            StringBuilder stringBuilder = new StringBuilder();
            stringBuilder.AppendLine("UPDATE PB_Buildingnumber ");
            stringBuilder.AppendLine($"SET StartingLayer ={data.StartingLayer},");
            stringBuilder.AppendLine($"TerminationLayer ={data.TerminationLayer} ");
            stringBuilder.AppendLine("WHERE");
            stringBuilder.AppendLine($"Id = '{data.BuliId}' ");
            stringBuilder.AppendLine("AND");
            stringBuilder.AppendLine($"ProjectId = '{data.ProjectId}'");
            await ExecuteSqlAsync(stringBuilder.ToString());
           
        }

        public async Task<PageResult<GetMaterialSummary>> GetMaterialSummary(PageInput<ProjectReturnConditionDTO> input)
        {
            StringBuilder sql = new StringBuilder();


         sql.AppendLine("   SELECT   ");
            sql.AppendLine(" OK.BuildingNumber,   ");
            sql.AppendLine(" OK.RealNums 'DesignQuantity',   ");
            sql.AppendLine("  OK.`MaterialCode` 'number',   ");
            sql.AppendLine(" OK.MaterialName 'ProductName',   ");
            sql.AppendLine(" OK.ParentId,   ");
            sql.AppendLine("OK.MaterialId,   ");
            sql.AppendLine("  OK.buildingID,   ");
            sql.AppendLine("OK.Spec,");
            sql.AppendLine("  OK.RealNums,   ");
            sql.AppendLine(" IFNULL(PB_PickUpList.IssuedQuantity, 0) IssuedQuantity,    "); 
        sql.AppendLine(" IFNULL(ReturnData.ReturnNums, 0) ReturnNums,    ");
            sql.AppendLine("  IFNULL(ReceiptWarehousing.ReceiptQuantity, 0) ReceiptQuantity   ");
            sql.AppendLine("  FROM   ");
            sql.AppendLine("  (   ");
            sql.AppendLine(" SELECT   ");
            sql.AppendLine(" SUM(Nums) AS 'Nums',   ");
            sql.AppendLine("SUM(RealNums) AS 'RealNums',   ");
            sql.AppendLine(" ParentId AS 'ParentId',   ");
            sql.AppendLine("  MaterialId AS 'MaterialId',   ");
            sql.AppendLine(" MaterialName AS 'MaterialName',   ");
            sql.AppendLine(" MaterialCode AS 'MaterialCode',   ");
            sql.AppendLine("  BuildingNumber AS 'BuildingNumber',   ");
            sql.AppendLine(" buildingID AS 'buildingID'  , ");
            sql.AppendLine("  Spec");
            sql.AppendLine(" FROM   ");
            sql.AppendLine("  (   ");
            sql.AppendLine("  SELECT   ");
            sql.AppendLine(" building.Buildingnumber BuildingNumber,   ");
            sql.AppendLine("  building.Id buildingID,   ");
            sql.AppendLine("   M.`Name` MaterialName,   ");
            sql.AppendLine("  M.`Code` MaterialCode,   ");
            sql.AppendLine(" M.Spec Spec, 	 ");
            sql.AppendLine("  a.ParentId,   ");
            sql.AppendLine("  a.MaterialId AS MaterialId,   ");
            sql.AppendLine("  a.Nums,   ");
            sql.AppendLine("  a.Slot,   ");
            sql.AppendLine(" a.RealNums,   ");
            sql.AppendLine("  a.State,   ");
            sql.AppendLine("  a.Id AS SubId   ");
            sql.AppendLine(" FROM   ");
            sql.AppendLine(" PB_DesignDetail AS a   ");
            sql.AppendLine(" LEFT JOIN PB_DesignList AS List ON a.ParentId = List.Id   ");
            sql.AppendLine(" LEFT JOIN PB_Material AS M ON a.MaterialId = M.Id   ");
            sql.AppendLine("LEFT JOIN PB_Buildingnumber AS building ON List.BuildingNumberID = building.Id   ");
            sql.AppendLine("  WHERE   ");
            sql.AppendLine(" a.State = 0   ");
            sql.AppendLine("  AND List.ProjectId = '"+input.Search.Keyword+"'   AND List.BuildingNumberID = '"+input.Search.Condition+"'   UNION ALL   ");
            sql.AppendLine("  SELECT   ");
            sql.AppendLine("   building.Buildingnumber AS 'BuildingNumber',   ");
            sql.AppendLine("   building.Id buildingID,   ");
            sql.AppendLine("     M.`Name` MaterialName,   ");
            sql.AppendLine("  M.`Code` MaterialCode,   ");
            sql.AppendLine(" M.Spec Spec, 	 ");
            sql.AppendLine("  a.ParentId,   ");
            sql.AppendLine(" a.MaterialId AS MaterialId,   ");
            sql.AppendLine("  a.Nums,   ");
            sql.AppendLine(" a.Slot,   ");
            sql.AppendLine("   a.RealNums,   ");
            sql.AppendLine("   a.State,   ");
            sql.AppendLine("  a.Id AS SubId   ");
            sql.AppendLine("   FROM   ");
            sql.AppendLine("  PB_DesignDetail AS a   ");
            sql.AppendLine(" LEFT JOIN PB_DesignList AS List ON a.ParentId = List.Id   ");
            sql.AppendLine("  LEFT JOIN PB_Material AS M ON a.MaterialId = M.Id   ");
            sql.AppendLine("  LEFT JOIN PB_Buildingnumber AS building ON List.BuildingNumberID = building.Id   ");
            sql.AppendLine("  WHERE   ");
            sql.AppendLine("  State = 1   ");
            sql.AppendLine("   AND List.ProjectId = '"+input.Search.Keyword+"'   AND List.BuildingNumberID = '"+input.Search.Condition+"' UNION ALL   ");
            sql.AppendLine("  SELECT   ");
            sql.AppendLine("   building.Buildingnumber AS 'BuildingNumber',   ");
            sql.AppendLine("    building.Id buildingID,   ");
            sql.AppendLine("   M.`Name` MaterialName,   ");
            sql.AppendLine("   M.`Code` MaterialCode,   ");
            sql.AppendLine(" M.Spec Spec, 	 ");
            sql.AppendLine("    a.ParentId,   ");
            sql.AppendLine("    a.MaterialId AS MaterialId,   ");
            sql.AppendLine("  (-a.Nums) AS Nums,   ");
            sql.AppendLine("   a.Slot,   ");
            sql.AppendLine(" (-a.RealNums) AS RealNums,   ");
            sql.AppendLine(" a.State,   ");
            sql.AppendLine(" a.Id AS SubId   ");
            sql.AppendLine("  FROM   ");
            sql.AppendLine("   PB_DesignDetail AS a   ");
            sql.AppendLine("  LEFT JOIN PB_DesignList AS List ON a.ParentId = List.Id   ");
            sql.AppendLine(" LEFT JOIN PB_Material AS M ON a.MaterialId = M.Id   ");
            sql.AppendLine("   LEFT JOIN PB_Buildingnumber AS building ON List.BuildingNumberID = building.Id   ");
            sql.AppendLine("   WHERE   ");
            sql.AppendLine("  a.State = 2   ");
            sql.AppendLine("  AND List.ProjectId = '"+input.Search.Keyword+"' AND List.BuildingNumberID = '"+input.Search.Condition+"'  ) AS k   ");
            sql.AppendLine("   GROUP BY   ");
            sql.AppendLine("  MaterialId,   ");
            sql.AppendLine("    ParentId,   ");
            sql.AppendLine(" MaterialName,   ");
            sql.AppendLine(" BuildingNumber,   ");
            sql.AppendLine("  MaterialCode,   ");
            sql.AppendLine("   buildingID ,  ");
            sql.AppendLine("Spec");
            sql.AppendLine("  ORDER BY   ");
            sql.AppendLine("  BuildingNumber,   ");
            sql.AppendLine("  MaterialCode   ");
            sql.AppendLine("    ) AS OK     ");
            sql.AppendLine("  LEFT JOIN(   ");
            sql.AppendLine(" SELECT   ");
            sql.AppendLine("  a.MaterialId AS MaterialId,   ");
            sql.AppendLine(" IFNULL(sum(a.ReadyNums ), 0 ) PreparedQuantity,   ");
            sql.AppendLine("  IFNULL(sum(C.OutNum), 0) IssuedQuantity,   ");
            sql.AppendLine("  List.ParentId   ");
            sql.AppendLine("  FROM   ");
            sql.AppendLine(" PB_PickUpList AS a   ");
            sql.AppendLine("  LEFT JOIN PB_DesignDetail AS List ON a.SubId = List.Id   ");
            sql.AppendLine("  LEFT JOIN TD_OutStorage AS TD_OutStorage ON a.ParentId = TD_OutStorage.RefCode   ");
            sql.AppendLine("  LEFT JOIN TD_OutStorRecord AS C ON TD_OutStorage.Id = C.OutStorId   ");
            sql.AppendLine("  AND C.MaterialId = List.MaterialId   ");
            sql.AppendLine(" GROUP BY   ");
            sql.AppendLine("  a.MaterialId,   ");
            sql.AppendLine("   ParentId   ");
            sql.AppendLine("  ) AS PB_PickUpList ON PB_PickUpList.ParentId = OK.ParentId   ");
            sql.AppendLine("   AND PB_PickUpList.MaterialId = OK.MaterialId   ");
            sql.AppendLine("  LEFT JOIN(   ");
            sql.AppendLine("  SELECT   ");
            sql.AppendLine("  IFNULL(Sum(returnss.ThisReturnNums ), 0 ) AS ReturnNums,   ");
            sql.AppendLine("   returnss.BuildingId,   ");
            sql.AppendLine("   returnss.MaterialId   ");
            sql.AppendLine("  FROM   ");
            sql.AppendLine("  PB_Return AS log   ");
            sql.AppendLine(" LEFT JOIN PB_ReturnDetailLog AS returnss ON returnss.ParentId = log.Id   ");
            sql.AppendLine("  GROUP BY   ");
            sql.AppendLine(" returnss.BuildingId,   ");
            sql.AppendLine("  returnss.MaterialId   ");
            sql.AppendLine("  ) AS ReturnData ON ReturnData.BuildingId = OK.buildingID   ");
            sql.AppendLine("   AND ReturnData.MaterialId = OK.MaterialId   ");
            sql.AppendLine("  LEFT JOIN(   ");
            sql.AppendLine(" SELECT   ");
            sql.AppendLine("  IFNULL(SUM(Detailtable.Num), 0) ReceiptQuantity,   ");
            sql.AppendLine("   Returntable.BuildingId,   ");
            sql.AppendLine("   Detailtable.MaterialId   ");
            sql.AppendLine(" FROM   ");
            sql.AppendLine("   TD_InStorDetail AS Detailtable   ");
            sql.AppendLine("   LEFT JOIN TD_InStorage AS Maintable ON Detailtable.InStorId = Maintable.Id   ");
            sql.AppendLine("  LEFT JOIN TD_Receiving AS Receivingtable ON Maintable.RecId = Receivingtable.Id   ");
            sql.AppendLine("   LEFT JOIN PB_Return AS Returntable ON Receivingtable.OrderId = Returntable.Id   ");
            sql.AppendLine("  GROUP BY   ");
            sql.AppendLine("  Returntable.BuildingId,   ");
            sql.AppendLine("  Detailtable.MaterialId   ");
            sql.AppendLine("  ) AS ReceiptWarehousing ON ReceiptWarehousing.BuildingId = OK.buildingID   ");
            sql.AppendLine("  AND ReceiptWarehousing.MaterialId = OK.MaterialId   ");
            var res = await Db.GetListBySqlAsync<GetMaterialSummary>(sql.ToString());

            int pagecount;
            List<GetMaterialSummary> listpro_down = res.ToList();
            List<GetMaterialSummary> productes = new List<GetMaterialSummary>();
            int pagees = listpro_down.Count;
            if (pagees % input.PageRows == 0)
            {
                pagecount = pagees / input.PageRows;
            }
            else
            {
                pagecount = (pagees / input.PageRows) + 1;
            }
            #region


            #endregion
            productes = listpro_down.Skip((input.PageIndex - 1) * input.PageRows).Take(input.PageRows).ToList();
            return new PageResult<GetMaterialSummary> { Data = productes, Total = pagees, };
        }

        #endregion

        #region 私有成员

        #endregion
    }

}
